package com.example.library.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.library.entity.BorrowRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 借阅记录Mapper接口
 *
   */
@Mapper
public interface BorrowRecordMapper extends BaseMapper<BorrowRecord> {

    /**
     * 分页查询借阅记录（带用户和图书信息）
     *
     * @param page      分页参数
     * @param userId    用户ID
     * @param bookId    图书ID
     * @param status    状态
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 借阅记录分页列表
     */
    IPage<BorrowRecord> selectBorrowRecordPage(Page<BorrowRecord> page,
                                               @Param("userId") Long userId,
                                               @Param("bookId") Long bookId,
                                               @Param("status") String status,
                                               @Param("startDate") LocalDateTime startDate,
                                               @Param("endDate") LocalDateTime endDate);

    /**
     * 查询用户当前借阅的图书
     *
     * @param userId 用户ID
     * @return 借阅记录列表
     */
    @Select("SELECT br.*, u.username, u.real_name as user_real_name, " +
            "b.title as book_title, b.author as book_author, b.isbn as book_isbn " +
            "FROM borrow_records br " +
            "LEFT JOIN users u ON br.user_id = u.id " +
            "LEFT JOIN books b ON br.book_id = b.id " +
            "WHERE br.user_id = #{userId} AND br.status = 'BORROWED' AND br.deleted = 0 " +
            "ORDER BY br.borrow_date DESC")
    List<BorrowRecord> selectCurrentBorrowsByUser(@Param("userId") Long userId);

    /**
     * 查询图书当前借阅记录
     *
     * @param bookId 图书ID
     * @return 借阅记录列表
     */
    @Select("SELECT br.*, u.username, u.real_name as user_real_name, " +
            "b.title as book_title, b.author as book_author, b.isbn as book_isbn " +
            "FROM borrow_records br " +
            "LEFT JOIN users u ON br.user_id = u.id " +
            "LEFT JOIN books b ON br.book_id = b.id " +
            "WHERE br.book_id = #{bookId} AND br.status = 'BORROWED' AND br.deleted = 0 " +
            "ORDER BY br.borrow_date DESC")
    List<BorrowRecord> selectCurrentBorrowsByBook(@Param("bookId") Long bookId);

    /**
     * 查询逾期的借阅记录
     *
     * @return 逾期借阅记录列表
     */
    @Select("SELECT br.*, u.username, u.real_name as user_real_name, " +
            "b.title as book_title, b.author as book_author, b.isbn as book_isbn " +
            "FROM borrow_records br " +
            "LEFT JOIN users u ON br.user_id = u.id " +
            "LEFT JOIN books b ON br.book_id = b.id " +
            "WHERE br.status = 'BORROWED' AND br.due_date < NOW() AND br.deleted = 0 " +
            "ORDER BY br.due_date ASC")
    List<BorrowRecord> selectOverdueRecords();

    /**
     * 查询即将到期的借阅记录
     *
     * @param days 提前天数
     * @return 即将到期的借阅记录列表
     */
    @Select("SELECT br.*, u.username, u.real_name as user_real_name, " +
            "b.title as book_title, b.author as book_author, b.isbn as book_isbn " +
            "FROM borrow_records br " +
            "LEFT JOIN users u ON br.user_id = u.id " +
            "LEFT JOIN books b ON br.book_id = b.id " +
            "WHERE br.status = 'BORROWED' " +
            "AND br.due_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL #{days} DAY) " +
            "AND br.deleted = 0 " +
            "ORDER BY br.due_date ASC")
    List<BorrowRecord> selectDueSoonRecords(@Param("days") Integer days);

    /**
     * 统计用户借阅次数
     *
     * @param userId 用户ID
     * @return 借阅次数
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE user_id = #{userId} AND deleted = 0")
    Long countBorrowsByUser(@Param("userId") Long userId);

    /**
     * 统计图书借阅次数
     *
     * @param bookId 图书ID
     * @return 借阅次数
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE book_id = #{bookId} AND deleted = 0")
    Long countBorrowsByBook(@Param("bookId") Long bookId);

    /**
     * 统计借阅记录数量
     *
     * @param status    状态
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 借阅记录数量
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE deleted = 0 " +
            "AND (#{status} IS NULL OR status = #{status}) " +
            "AND (#{startDate} IS NULL OR borrow_date >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR borrow_date <= #{endDate})")
    Long countBorrowRecords(@Param("status") String status,
                            @Param("startDate") LocalDateTime startDate,
                            @Param("endDate") LocalDateTime endDate);

    /**
     * 获取借阅统计数据
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 统计数据
     */
    List<Map<String, Object>> getBorrowStatistics(@Param("startDate") LocalDateTime startDate,
                                                   @Param("endDate") LocalDateTime endDate);

    /**
     * 获取热门图书统计
     *
     * @param limit     限制数量
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 热门图书统计
     */
    List<Map<String, Object>> getPopularBooksStatistics(@Param("limit") Integer limit,
                                                         @Param("startDate") LocalDateTime startDate,
                                                         @Param("endDate") LocalDateTime endDate);

    /**
     * 获取活跃用户统计
     *
     * @param limit     限制数量
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 活跃用户统计
     */
    List<Map<String, Object>> getActiveUsersStatistics(@Param("limit") Integer limit,
                                                        @Param("startDate") LocalDateTime startDate,
                                                        @Param("endDate") LocalDateTime endDate);

    /**
     * 更新逾期状态
     *
     * @return 更新数量
     */
    @Update("UPDATE borrow_records SET status = 'OVERDUE' " +
            "WHERE status = 'BORROWED' AND due_date < NOW() AND deleted = 0")
    Integer updateOverdueStatus();

    /**
     * 检查用户是否已借阅该图书
     *
     * @param userId 用户ID
     * @param bookId 图书ID
     * @return 借阅记录
     */
    @Select("SELECT * FROM borrow_records " +
            "WHERE user_id = #{userId} AND book_id = #{bookId} " +
            "AND status = 'BORROWED' AND deleted = 0 LIMIT 1")
    BorrowRecord checkUserBookBorrow(@Param("userId") Long userId, @Param("bookId") Long bookId);

    /**
     * 统计用户当前借阅数量
     *
     * @param userId 用户ID
     * @return 当前借阅数量
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE user_id = #{userId} AND status = 'BORROWED' AND deleted = 0")
    Long countUserCurrentBorrow(@Param("userId") Long userId);

    /**
     * 统计用户总借阅数量
     *
     * @param userId 用户ID
     * @return 总借阅数量
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE user_id = #{userId} AND deleted = 0")
    Long countUserTotalBorrow(@Param("userId") Long userId);

    /**
     * 统计图书借阅数量
     *
     * @param bookId 图书ID
     * @return 借阅数量
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE book_id = #{bookId} AND deleted = 0")
    Long countBookBorrow(@Param("bookId") Long bookId);

    /**
     * 统计归还记录数量
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 归还记录数量
     */
    @Select("SELECT COUNT(*) FROM borrow_records WHERE status = 'RETURNED' AND deleted = 0 " +
            "AND return_date >= #{startDate} AND return_date <= #{endDate}")
    Long countReturnRecords(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

}